﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Common;
using System.Data.OleDb;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Threading;
using System.Windows;
using Microsoft.Office.Interop.Excel;

namespace Zuordnung {
    class zCodeUnebenutzter {

        //http://blogs.msdn.com/b/david.wang/archive/2006/03/26/howto-detect-process-bitness.aspx
        void ist_das32_oder_64_bit() {
            String arch0 = System.Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE", EnvironmentVariableTarget.Machine);
            String arch1 = System.Environment.GetEnvironmentVariable("PROCESSOR_ARCHITEW6432", EnvironmentVariableTarget.Machine);
            switch (arch0) {
                case "x86":
                    //..
                    break;
                default:
                    //todo
                    break;
            }
        }



        static OleDbConnection _dbconn = null;
        static public Microsoft.Office.Interop.Excel.Application xlapp = null;

        // todo schleife sparen
        public static int DBSelectQuery(string queryString) {
            OleDbCommand command = new OleDbCommand(queryString, _dbconn);
            OleDbDataReader reader = command.ExecuteReader();
            int result = 0;
            while (reader.Read()) {
                result++;
            }
            // always call Close when done reading.
            reader.Close();
            return result;
        }




        /*
            string mm = String.Format("Schema: {0}\n", DBOleDBCConn.GetSchema().ToString());
            mm += String.Format("DataSource: {0} \n", DBOleDBCConn.DataSource);
            mm += String.Format("Database: {0}\n", DBOleDBCConn.Database);
            mm += String.Format("State: {0}\n ", DBOleDBCConn.State);
            MessageBox.Show(mm);
            */

        /// <summary>
        /// Für das Umkopieren über memarray
        /// </summary>
        public static void DBDelData() {
            string queryString = "DELETE FROM Zoo";
            OleDbCommand command = new OleDbCommand(queryString, _dbconn);
            int result = command.ExecuteNonQuery();
        }





        /// <summary>
        /// Das eingebaute Save-as-XML ist wenig nützlich: ich müsste mein eigenes XML erzeugen. 
        /// Weg über ADODB ist attraktiver.
        /// </summary>
        /// <param name="xlwb"></param>
        public static void xls2dbViaXML__WorkInProgress(Workbook xlwb) {
            foreach (Worksheet xlws in xlwb.Worksheets) {
                xlws.SaveAs("c:\\markusExcelSavedAsXML_" + xlws.Name + ".xml", XlFileFormat.xlXMLSpreadsheet);
            }
        }






        //xlapp = new Microsoft.Office.Interop.Excel.Application();
        //xlwb = xlapp.Workbooks.Open(fname);
        //xls2dbViaMemarray(xlwb);
        //xls2dbViaXML__WorkInProgress(xlwb);
        //xlwb.Close(SaveChanges: false);
        //xlapp.Quit();


        public static void zzzzzXls2dbViaMemarray(String ffname) {
            Workbook xlwb = xlapp.Workbooks.Open(ffname);
            Range range, lastrange;
            String report = "";
            //DBDelData();
            foreach (Worksheet xlws in xlwb.Worksheets) {
                if (xlws.Name.StartsWith("Pivot")) {
                    report += "Arbeitsblatt " + xlws.Name + " für Import übergangen\n";
                    continue;
                }

                lastrange = xlws.get_Range("J6").get_End(XlDirection.xlDown);
                int lastrow = lastrange.Row;
                ///        xls ====> MEMARRAY  
                range = xlws.get_Range("A2", "Q" + lastrange.Row.ToString()); // Q == 17
                object[,] memarray = range.get_Value();
                ///        MEMARRAY  ====> DB
                for (int row = 1; row <= lastrange.Row - 1; row++) {
                    string sid = memarray[row, 1].ToString();
                    sid = sid.TrimEnd();
                    int client = Int32.Parse(memarray[row, 3].ToString()); // todo try catch
                    string ug = memarray[row, 9].ToString();
                    string userID = memarray[row, 4].ToString();
                    String firstName = memarray[row, 10].ToString();
                    String lastName = memarray[row, 11].ToString();
                    string clt = memarray[row, 13].ToString();
                    zzzDBInsertData(sid, client, ug, userID, firstName, lastName, clt, row, xlws.Name);
                }
            }
            xlwb.Close(SaveChanges: false);
        }


        public static void zzzDBInsertData(string sid, int client, string ug, string userID, string firstName, string lastName, string clt, int oriSpalte, string oriReiter) {
            //todo mit {0} arbeiten
            // todo ' im Namen vernichtet
            string queryString = "INSERT INTO Zoo (SID, Client, UG, userID, firstName, lastName, CLT, oriSpalte, oriReiter) VALUES ('" +
                sid + "', " + client + ", '" + ug.TrimEnd() + "', '" + userID.TrimEnd() + "', '" + firstName.TrimEnd().Replace("'", " ") + "', '" + lastName.TrimEnd().Replace("'", " ") + clt.TrimEnd() + "', '" + "', " + oriSpalte + ", '" + oriReiter + "')";
            OleDbCommand command = new OleDbCommand(queryString, _dbconn);
            int result = command.ExecuteNonQuery();
        }



    }
}
